package org.jeecg.modules.demo.DPT.job;

import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.kingdee.bos.webapi.sdk.K3CloudApi;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.modules.demo.DPT.entity.Ticket;
import org.jeecg.modules.demo.DPT.service.ITicketService;
import org.jeecg.modules.demo.DptAccount.entity.DptAccount;
import org.jeecg.modules.demo.DptAccount.service.IDptAccountService;
import org.jeecg.modules.demo.TicketDataString.entity.TicketDataString;
import org.jeecg.modules.demo.TicketDataString.service.ITicketDataStringService;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URI;
import java.net.http.HttpClient;
import java.net.http.HttpRequest;
import java.net.http.HttpResponse;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 示例不带参定时任务
 * 
 * @Author Scott
 */
@Slf4j
@Component
public class TestJob implements Job {

	@Autowired
	ITicketService ticketService;
	@Autowired
	ITicketDataStringService ticketDataStringService;
	@Autowired
	IDptAccountService dptAccountService;
	@SneakyThrows
	@Override
	public void execute(JobExecutionContext jobExecutionContext) {
		// 获取当前日期
		Calendar calendar = Calendar.getInstance();

		// 格式化今天日期
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String todayStr = sdf.format(calendar.getTime());
		String todayStr1 = "2026-1-1";

		// 获取昨天日期
		calendar.add(Calendar.DAY_OF_MONTH, -1);
		String yesterdayStr = sdf.format(calendar.getTime());
		String yesterdayStr1 = "2020-1-1";

		String parameter = jobExecutionContext.getMergedJobDataMap().getString("parameter");
		if (parameter!=null){
			yesterdayStr=parameter;
		}
		String filepath="C:\\Users\\Administrator\\Desktop\\对账单模板-梦娇.xlsx";
//		ImportParams params = new ImportParams();
//		params.setTitleRows(1);
//		params.setHeadRows(1);
//		long start = new Date().getTime();
//		List<Map> list = ExcelImportUtil.importExcel(
//				new File(filepath),
//				Map.class, params);
//		System.out.println(new Date().getTime() - start);
//		System.out.println(list.size());
//		System.out.println(ReflectionToStringBuilder.toString(list.get(0)));

		K3CloudApi api = new K3CloudApi();
// 从文件获取输入流
		InputStream inputStream = new FileInputStream(filepath);
		List<Map<String, Object>> result = convertExcelToMapList(inputStream);
		List excelList=new ArrayList<>();

		JSONObject excelMap = new JSONObject();
		for (Map<String, Object> item : result) {
			String purchaseno = item.get("采购订单号").toString();
			String materialNo = item.get("商品编码").toString();
			String index = item.get("序号").toString();
			BigDecimal materialCountInExcel = new BigDecimal(item.get("数量").toString());
			JSONObject purchase = excelMap.getJSONObject(purchaseno);
			if(purchase==null){
				purchase = new JSONObject();
				JSONObject material=new JSONObject();
				JSONArray indexList = new JSONArray();
				indexList.add(index);
				material.put("indexList",indexList);
				material.put("materialCountInExcel",materialCountInExcel);
				material.put("materialCountInKD",BigDecimal.ZERO);
				purchase.put(materialNo,material);
				excelMap.put(purchaseno,purchase);
			}else {
				JSONObject material=purchase.getJSONObject(materialNo);
				if(material==null){
					material=new JSONObject();
					JSONArray indexList = new JSONArray();
					indexList.add(index);
					material.put("indexList",indexList);
					material.put("materialCountInExcel",materialCountInExcel);
					material.put("materialCountInKD",BigDecimal.ZERO);
					purchase.put(materialNo,material);
				}else {
					material.getJSONArray("indexList").add( index);
					material.put("materialCountInExcel",material.getBigDecimal("materialCountInExcel").add(materialCountInExcel));
				}
			}
		}
		excelMap.forEach((purchaseno,purchase) -> {
			String json1 = String.format("{\n" +
					"\"FormId\": \"PUR_ReceiveBill\",\n" +
					"\"FieldKeys\": \"FID \",\n" +
					"\"FilterString\": \"F_EC_SoureceNumber LIKE '%s'\",\n" +
					"\"OrderString\": \"\",\n" +
					"\"TopRowCount\": 0,\n" +
					"\"StartRow\": 0,\n" +
					"\"Limit\": 2000,\n" +
					"\"SubSystemId\": \"\"\n" +
					"}",purchaseno);
            try {
                String ReceiveBillStr = String.valueOf(api.billQuery(json1));
				List<JSONObject>  ReceiveBills= JSONArray.parseArray(ReceiveBillStr, JSONObject.class);
				for (JSONObject ReceiveBill : ReceiveBills) {
					String FSBillId = ReceiveBill.getString("FID");
					String json = String.format("{\n" +
						"    \"FormId\": \"STK_InStock\",\n" +
						"    \"FieldKeys\": \"FMaterialId,FMustQty,FRealQty,FBILLNO,FDocumentStatus\",\n" +
						"    \"FilterString\": \"FInStockEntry_Link_FSBillId = %S AND FSTABLENAME='T_PUR_ReceiveEntry' AND FDocumentStatus='C'\",\n" +
						"    \"OrderString\": \"\",\n" +
						"    \"TopRowCount\": 0,\n" +
						"    \"StartRow\": 0,\n" +
						"    \"Limit\": 2000,\n" +
						"    \"SubSystemId\": \"\"\n" +
						"}",FSBillId);
					String InStockStr = String.valueOf(api.billQuery(json));
					List<JSONObject> InStocks = JSONArray.parseArray(InStockStr, JSONObject.class);
					for (JSONObject InStock : InStocks) {
						String materialId = InStock.getString("FMaterialId");
	//					String FMustQty = InStock.getString("");
						BigDecimal FRealQty = InStock.getBigDecimal("FRealQty");
						String FBILLNO = InStock.getString("FBILLNO");
						String materialjson = String.format("{\n" +
									"    \"FormId\": \"BD_MATERIAL\",\n" +
									"    \"FieldKeys\": \"FNUMBER,FName\",\n" +
									"    \"FilterString\": \"FMATERIALID = %S\",\n" +
									"    \"OrderString\": \"\",\n" +
									"    \"TopRowCount\": 0,\n" +
									"    \"StartRow\": 0,\n" +
									"    \"Limit\": 2000,\n" +
									"    \"SubSystemId\": \"\"\n" +
									"}",materialId);
						String materialStr = api.billQuery(materialjson);
						List<JSONObject> materials = JSONArray.parseArray(materialStr, JSONObject.class);
						String materialNoInKD = materials.get(0).getString("FNUMBER");
						String materialNameInKD = materials.get(0).getString("FName");
						if(((JSONObject) purchase).getJSONObject(materialNoInKD)!=null){
							BigDecimal materialCountInKD = ((JSONObject) purchase).getJSONObject(materialNoInKD).getBigDecimal("materialCountInKD");
							((JSONObject)purchase).getJSONObject(materialNoInKD).put("materialCountInKD",materialCountInKD.add(FRealQty));
						}
					}
				}
            } catch (Exception e) {
                throw new RuntimeException(e);
            }
        });
		excelMap.forEach((purchaseno,purchase) -> {
			((JSONObject)purchase).forEach((materialNo,material) -> {
				BigDecimal materialCountInKD = ((JSONObject) material).getBigDecimal("materialCountInKD");
				BigDecimal materialCountInExcel = ((JSONObject) material).getBigDecimal("materialCountInExcel");
				if(materialCountInKD.compareTo(materialCountInExcel)!=0){
					String diffMsg = purchaseno+"-"+materialNo+"=>"+" 不匹配，差异：(入库数量"+materialCountInKD+"-发货数量"+materialCountInExcel+")="+materialCountInKD.subtract(materialCountInExcel);
					System.out.println(diffMsg);
					// 获取该物料对应的所有行号
					JSONArray indexList = ((JSONObject) material).getJSONArray("indexList");
					for(int i=0; i<indexList.size(); i++){
						String index = indexList.getString(i);
						// 在result列表中找到对应行并添加差异信息
						for(Map<String, Object> row : result){
							if(row.get("序号").toString().equals(index)){
								row.put("差异信息", diffMsg);
								break;
							}
						}
					}
				}
			});
		});

// 在原Excel文件的P列写入差异信息
		try {
			// 重新读取原Excel文件
			FileInputStream fis = new FileInputStream("C:\\Users\\Administrator\\Desktop\\对账单模板-梦娇.xlsx");
			XSSFWorkbook workbook = new XSSFWorkbook(fis);
			Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

			// 获取标题行
			Row headerRow = sheet.getRow(5); // 根据您的ImportParams设置，标题行在第6行

			// 检查P列是否有标题，如果没有则添加
			Cell pHeaderCell = headerRow.getCell(15); // P列是第16列(0-based)
			if(pHeaderCell == null || pHeaderCell.getStringCellValue().isEmpty()){
				pHeaderCell = headerRow.createCell(15);
				pHeaderCell.setCellValue("差异信息");
			}

			// 写入差异信息到P列
			for(Map<String, Object> rowData : result){
				String index = rowData.get("序号").toString();
				int indexInt = (new BigDecimal(index)).intValue();
				String diffInfo = (String) rowData.get("差异信息");

				if(diffInfo != null){
					// 找到对应的行(序号从6开始，因为前5行是标题)
					int rowNum = indexInt + 5;
					Row row = sheet.getRow(rowNum);
					if(row == null){
						row = sheet.createRow(rowNum);
					}

					Cell pCell = row.getCell(15);
					if(pCell == null){
						pCell = row.createCell(15);
					}
					pCell.setCellValue(diffInfo);
				}
			}

			// 保存修改
			FileOutputStream fos = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\对账单模板-梦娇.xlsx");
			workbook.write(fos);
			fos.close();
			workbook.close();
			fis.close();

			System.out.println("差异信息已写入原Excel文件的P列");
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println(1);
	}

	public List<Map<String, Object>> convertExcelToMapList(InputStream fileStream) {
		List<Map<String, Object>> resultList = new ArrayList<>();

		// 使用AutoPoi导入Excel数据
		ImportParams params = new ImportParams();
		params.setTitleRows(6); // 跳过标题行
		params.setHeadRows(5);

		try {
			List<Map<String, Object>> dataList = ExcelImportUtil.importExcel(
					fileStream,
					Map.class,
					params
			);
			System.out.println(1);

			// 筛选A列含数字的行并转换
			for(Map<String, Object> row : dataList) {
				Object colA = row.get("序号"); // 假设A列对应key为"A"
				if(colA != null && String.valueOf(colA).matches(".*\\d+.*")) {
					resultList.add(row);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return resultList;
	}


}
